Global - Stored Procedures

Stored Procedures can be used to automate the execution of operations on the database, such as updating or editing records, making a selection of records, deleting records, or a mix of all of these.

There are two types of Stored Procedures :

• General — These Stored Procedures can be used in :
  • SQL tasks
  • Data load tasks
  • Data export tasks

  Note: What this type of Stored Procedure does is dependent on the type of job that is selected. In an Export task, the Stored Procedure will define the main data set to export where in an Import task it will define how data is migrated from a specific source to the destination table. In an SQL task, you will mainly transform data and the stored procedure will contain the actions that are needed to make modifications to the data set.

  • A Custom Component used in a journey. As a result, it is possible to execute a Stored Procedure and perform database tasks in a journey.
• Bounce — These Stored Procedures are available from the Email quality configuration for Audience Lists. They can never be selected in a task.

Note: The user needs explicit rights to create Stored Procedures.

 

Stored Procedures created through the platform have the following characteristics:

• Logging is activated by default.
• Scheduling can be handled by platform admins, rather than SQL developers.
• Greater control over the stored Procedure’s execution – reducing risk of incorrect or bad code causing production down-time.

 

The Stored Procedures overview shows all configured stored procedures.
The Type column indicates if the Stored Procedure is a General Stored Procedure or a Bounce Stored Procedure.

From this overview you can

• Create a new stored procedure — See below.
• Edit an existing stored procedure — By clicking on a stored procedure name. The properties are then displayed in a right sliding panel.
• Delete an existing stored procedure — By clicking on the bin icon.
• View the usage of the medium — By clicking on the road sign. The usage is shown in the right sliding panel.
  
  Usage is split per tab :
  - Each tab corresponds to a different usage type (for example 'Tasks').
  - The number of usages per type is shown between brackets (for example 'Tasks (10)').
  
  You can click on a row to jump directly to a specific usage location (for example to a task).
  When the Stored Procedure is used in an organization in Marigold Engage to which the user has no access, this is mentioned too (for example: '11 asset(s) in organization(s) that you do not have access to').
  If a stored procedure is not used at all, a corresponding message is shown.
  
  

  Example of a stored procedure that is used in 2 Custom Components, from which there's '1 asset(s) in organization(s) that you do not have access to' :
  

  
  
  

Create Stored Procedure

To create a new stored procedure, click on the New button at the top-right.

You can choose one of the following types :

• General Stored Procedure — These Stored Procedures can be used in tasks or Custom Components in a journey. The Custom Component itself is configured in the Data Exchange section.
• Bounce Stored Procedure — These procedures can only be used in Email Quality settings for a Audience List. They let the user perform specific actions after a bounce, such as updating multiple Audience Lists at a time when an email bounces.

In the right sliding panel, the following fields can be configured (the ones with an asterisk are required fields) :

• Name* — Provide a name for the Stored Procedure. The name must start with ST_ and can only contain alphanumerical characters and underscores. Once the Stored Procedure is saved, the name can no longer be modified.
• Description — Provide an optional description for the Stored Procedure.
• Content — In the numbered code box, enter the SQL code for the Stored Procedure.

For Bounce Stored Procedures, the header contains a fixed part with parameters. The parameters cannot be edited and they are filled out automatically when the Bounce Stored Procedure is called. Below is what is added automatically to the Bounce Stored Procedure:

Technical note:
CREATE (or ALTER) PROCEDURE [NAME_OF_PROCEDURE]
@LISTID INT,
@USERID INT,
@EMAIL NVARCHAR(255),
@BOUNCE_THRESHOLD_REASON NVARCHAR(50),
@INQUEUEID BIGINT = -1,
@STATE INT = -1
AS

Where @BOUNCE_THRESHOLD_REASON can have one of the following values:
- SYNTAX
- DOMAIN
- SOFTBOUNCE
- HARDBOUNCE
- COMPLAINT
Unknown is linked to asynchronous bounces.

A standard documentation header is provided by default. We advise editing this header and filling in the parameters, such as Author, CreationDate, etc., in every Stored Procedure for the following reasons:

• All changes done to routines are logged in a custom version control system and these headers are parsed and stored for easy retrieval of changes.
• The headers will help new developers to quickly grasp what the routine is supposed to do.
• It makes it easier to find the author of a routine.

Technical note: Use the toggle button in the upper right corner to display the content in full screen.

Note: For more information on the Stored Procedure documentation header, please check out this topic.

When done, Save the Stored Procedure.

Example : Setup and use a Custom Component of type Stored Procedure